package com.project.common.utils;

import com.project.entity.InternInfo;
import com.project.entity.OrgInfo;
import com.project.entity.StudentInfo;
import com.project.entity.User;
import com.project.myBeans.InternReportEntity;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;

/**
 * author: WheelChen
 * data: 2017/7/21
 * function: 导入excel信息
 */
public class ExcelUtil {

    /**
     * 解析
     * 第一行是标题行
     * 第二行以后都是内容
     * 例如：
     * id sex name
     * 1   男     王
     * 2   女     李
     * <p>
     * <p>
     * 解析后的map格式：
     * key  value
     * 0     List()一行
     * 1     List()一行
     * 2     List()一行
     * <p>
     * 例如：
     * 0    [id ,  name, sex,   sal   ,  date]
     * 1    [1.0, wang, 1.0, 1000.0, 42287.0]
     * 2    [2.0, liang, 1.0, 1001.0, 42288.0]
     *
     * @param in 输入流
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static Map parse(InputStream in) throws IOException, InvalidFormatException {
        //提取并创建工作表
        Workbook wb = createWorkbook(in);
        //获取sheet页第0页
        Sheet sheet = wb.getSheetAt(0);
        //获取行迭代器
        Iterator rows = sheet.rowIterator();
        //解析出来的数据存放到这个map里面，value套了一层List，key用于存放标题，List用于存放标题下的所有数据
        Map<String, List<String>> map = new HashMap<String, List<String>>();

        String title = null;//标题
        int rowindex = 0;//行数
        int cellindex = 0;//列数
        String data = null;//用于暂存数据
        while (rows.hasNext()) {
            List<String> list = new ArrayList<String>();
            cellindex = 0;
            //获取行中数据
            Row row = (Row) rows.next();
            //获取列迭代器
            Iterator cells = row.cellIterator();
            while (cells.hasNext()) {
                //获取列中数据
                Cell cell = (Cell) cells.next();
                //获取每个单元格的值
                //将标题下的内容放到list中
                list.add(getValue4Cell(cell));
            }
            //将解析完的一列数据压入map
            map.put("" + rowindex++, list);
        }
        in.close();
        return map;
    }


    /**
     * 格式：
     * key      value
     * <p>
     * id      [1.0, 2.0, 3.0, 4.0, 5.0]
     * sex     [1.0, 1.0, 1.0, 0.0, 0.0]
     * name    [wang, liang, eguid, qq, yy]
     * date    [42287.0, 42288.0, 42289.0, 42290.0, 42291.0]
     * sal     [1000.0, 1001.0, 1002.0, 1003.0, 1004.0]
     *
     * @throws InvalidFormatException
     */
    public static Map<String, List<String>> parseByfield(InputStream in, String[] fields) throws InvalidFormatException, IOException {
        Workbook wb = createWorkbook(in);
        //excel第一页
        Sheet sheet = wb.getSheetAt(0);
        //列
        Cell cell = null;
        //暂时存放
        String data = null;
        //最大行数
        int maxRowNum = sheet.getLastRowNum();
        //最大列数
        int MaxCellNum = sheet.getRow(0).getLastCellNum();

        List<String> list = null;

        Map<String, List<String>> map = null;

        map = new HashMap<String, List<String>>();

        for (int i = 0; i < MaxCellNum; i++) {

            list = new ArrayList<String>();
            String title = null;
            for (int j = 0; j <= maxRowNum; j++) {
                //获取第j行第i列的值
                cell = sheet.getRow(j).getCell(i);
                if (cell == null) {
                    continue;
                }
                data = getValue4Cell(cell);
                if (j == 0) {
                    title = data;
                }
                //如果标题与给定字段对应,则记录值;否则进入下个整列
                if (ishave(fields, title)) {
                    if (j != 0) {
                        list.add(data);
                    }
                } else {
                    break;
                }

            }
            map.put(title, list);
        }
        return map;
    }

    /**
     * 是否有此字段
     *
     * @param fields
     * @param field
     * @return
     */
    private static boolean ishave(String[] fields, String field) {
        if (field == null || fields == null || fields.length < 1) {
            return false;
        }

        for (int index = 0; index < fields.length; index++) {
            if (field.equals(fields[index]))
                return true;
        }
        return false;
    }


    /**
     * 提取单元格中的值
     */
    private static String getValue4Cell(Cell cell) {
        String data = null;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: // 数字 (防止以科学计数法)
                cell.setCellType(Cell.CELL_TYPE_STRING);
                data = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                data = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                data = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                data = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: // 空值
                data = String.valueOf("");
                break;
            case Cell.CELL_TYPE_ERROR: // 故障
                System.out.println("故障");
                break;
            default:
                System.out.print("未知类型");
                break;
        }
        return data;
    }

    /**
     * 提取文件并创建工作表
     *
     * @throws IOException
     * @throws InvalidFormatException
     */
    private static Workbook createWorkbook(InputStream in) throws InvalidFormatException, IOException {
        //如果创建工作表失败会抛出IO异常
        Workbook wb = WorkbookFactory.create(in);
        return wb;
    }

    /**
     * 提取文件并创建工作表
     *
     * @throws IOException
     * @throws InvalidFormatException
     */
    private static Workbook createWorkbook(File file) throws InvalidFormatException, IOException {
        //如果文件不存在，抛出文件没找到异常
        InputStream input = new FileInputStream(file);

        Workbook wb = null;
        //如果创建工作表失败会抛出IO异常
        wb = WorkbookFactory.create(input);
        return wb;
    }

    /**
     * @param wb        excel表
     * @param sheetName 单个sheet的名称
     * @return excel表
     * @author WheelChen
     * date: 2017/8/11
     * function: 初始化excel创建第一张表单的名称
     */
    public static void createSheet(Workbook wb, String sheetName) {
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet sheet = wb.createSheet(safeName);
    }

    /**
     * author: WheelChen
     * date: 2017/8/11
     * function: 创建标题
     *
     * @param sheet
     * @param Titles
     * @return
     */
    public static void createTitle(Sheet sheet, List<String> Titles) {
        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow((short) 0);
        // Create a cell and put a value in it.
        for (int i = 0; i < Titles.size(); i++) {
            row.createCell(i).setCellValue(Titles.get(i));
        }
    }

    /**
     * author: WheelChen
     * date: 2017/8/13
     * function:
     *
     * @param file
     * @param errorData
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static void importErrorData(File file, Map<String, List<String>> errorData) throws IOException, InvalidFormatException {
        Workbook wb = new XSSFWorkbook();
        createSheet(wb, "导入失败数据");
        Sheet sheet = wb.getSheetAt(0);
        createTitle(sheet, errorData.get("title"));
        errorData.remove("title");
        short index = 1;
        for (Map.Entry<String, List<String>> e : errorData.entrySet()) {
            List<String> data = e.getValue();
            if (data != null) {
                Row row = sheet.createRow(index);
                for (int i = 0; i < data.size(); i++) {
                    row.createCell(i).setCellValue(data.get(i));
                }
                index++;
            }
        }
        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
        System.out.println("写入excle成功");
    }

    /**
     *
     * 导入文件进入excel
     *
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static Workbook importInterInfoData(String sheetName, List<InternReportEntity> dataList, short startIndex) throws IOException, InvalidFormatException {
        Workbook wb =  new XSSFWorkbook();
        importInterInfoData(wb, sheetName, dataList, startIndex);
        return wb;
    }

    /**
     *
     * 导入文件进入excel
     *
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static Workbook importInterInfoData(File file, String sheetName, List<InternReportEntity> dataList, short startIndex) throws IOException, InvalidFormatException {
        Workbook wb =  createWorkbook(file);
        importInterInfoData(wb, sheetName, dataList, startIndex);
        return wb;
    }

    /**
     *
     * 导入文件进入excel
     *
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static Workbook importInterInfoData(Workbook wb, String sheetName, List<InternReportEntity> dataList, short startIndex) throws IOException, InvalidFormatException {
        //创建excle
        createSheet(wb, sheetName);
        //创建第一页
        Sheet sheet = wb.getSheetAt(0);
        //创建
//        createTitle(sheet, data.get("title"));
//        data.remove("title");

        short index = startIndex;
        for (InternReportEntity data : dataList) {
            if (data != null) {
                User user = data.getUser();
                StudentInfo studentInfo = data.getStudentInfo();
                InternInfo internInfo = data.getInternInfo();
                OrgInfo orgInfo = data.getOrgInfo();

                Row row = sheet.createRow(index);

                row.createCell(0).setCellValue(data.getClassName());
                if (user != null) {
                    //设置工号
                    row.createCell(1).setCellValue(user.getWorkid());
                    //姓名
                    row.createCell(2).setCellValue(user.getRealname());
                    //性别
                    if (user.getSex() != null) {
                        String sex =  1 == user.getSex()? "男" : "女";
                        row.createCell(3).setCellValue(sex);
                    }

                    //联系电话
                    row.createCell(4).setCellValue(user.getMobilephone());
                }
                //校内指导老师
                row.createCell(5).setCellValue(data.getTeacherName());

                if (orgInfo != null) {
                    //实习单位名称
                    row.createCell(6).setCellValue(orgInfo.getFullname());
                    //是否为实习基地
                    if (orgInfo.getIsPracticeBase() != null) {
                        String res = orgInfo.getIsPracticeBase() ? "是" : "否";
                        row.createCell(7).setCellValue(res);
                    }

                }
                if (internInfo != null) {
                    //实习类型
                    if (internInfo.getType() != null) {
                        row.createCell(8).setCellValue(internInfo.getType());
                    }
                    //实习单位指导老师
                    row.createCell(9).setCellValue(internInfo.getInstructor());
                    //实习单位指导老师联系电话
                    row.createCell(10).setCellValue(internInfo.getInstructorphone());
                }

            }

            index++;
        }

        CellRangeAddress cra =new CellRangeAddress(index, index, 0, 11);
        sheet.addMergedRegion(cra);
        Row row = sheet.createRow(index);
        row.createCell(0).setCellValue("备注：1.本表按专业填报，其中标“*”栏必须由学院填写或核对；实习类型编号：1.基地集中实习2.校内实训实习3.自主分散实习4.学生创业实践");
        index++;

        Row row2 = sheet.createRow(index);
        CellRangeAddress cra2 =new CellRangeAddress(index, index, 0, 11);
        sheet.addMergedRegion(cra2);
        row2.createCell(0).setCellValue("2.实习单位名称需填写全称，联系人需填写确切姓名，联系人电话首选单位固定电话。");
        return wb;
    }


}
